Create Table

Creating a New Table

When creating a new table in the database, you can define various columns with different properties, such as data types, constraints, and default values. Below are a few examples showcasing how to use the table creation UI.

Table Name and Description

• Table Name: The name of the table is entered at the top of the form (e.g., admin).
• Description: You can provide a description of the table, explaining its purpose.

Example 1: Creating a Basic Table

Here’s an example of a simple table definition for a users table:

• Table Name: users
• Description: Stores basic information of users.

Column Name	Data Type	Properties	Comments	Default Value
id	int	Primary Key, Auto Increment, Not Null	Unique identifier for the user
username	varchar(50)	Not Null	User’s login name
email	varchar(100)	Unique, Not Null	User’s email address
password_hash	varchar(255)	Not Null	Hashed password for security
created_at	timestamp	Not Null	Account creation timestamp	CURRENT_TIMESTAMP

• + Add Column: Add additional columns for more user information.
• + Add Index: You can add an index on the email column to optimize searches for user email addresses.

Properties

• Primary Key: The id column is marked as the primary key.
• Auto Increment: The id is auto-incremented for each new row.
• Not Null: Ensures that all important fields (e.g., username, email, and password_hash) cannot be empty.

Example 2: Creating an Orders Table

Here’s an example of a table that stores orders information:

• Table Name: orders
• Description: Stores order information for the e-commerce platform.

ColumnName	Data Type	Properties	Comments	Default Value
order_id	int	Primary Key, Auto Increment, Not Null	Unique identifier for eachorder
user_id	int	Foreign Key (references users.id), Not Null	Links order to a user
total_amount	decimal (10,2)	Not Null	Total cost of the order
order_status	varchar (20)	Not Null	Status of the order	pending
order_date	timestamp	Not Null	Date and time of order creation	CURRENT_TIMESTAMP
shipping_date	timestamp	Null	Expected shipping date

Properties

• Primary Key: The order_id column is marked as the primary key.
• Foreign Key: The user_id column references the id column in the users table.
• Not Null: Key fields like user_id, total_amount, order_status, and order_date are mandatory.
• Default Value: The order_status is set to pending by default when a new order is created.

Adding an Index

• Index on user_id: You can add an index on user_id to optimize the retrieval of orders for a particular user.
• Composite Index: A composite index on user_id and order_date can be added to optimize searching for orders by a specific user within a certain date range.

Example 3: Creating a Products Table with Different Data Types

Here’s an example of a table that stores product information:

• Table Name: products
• Description: Stores product details for an e-commerce platform.

Column Name	Data Type	Properties	Comments	Default Value
product_id	int	Primary Key, Auto Increment, Not Null	Unique identifier for each product
product_name	varchar(100)	Not Null	Name of the product
description	text	Null	Description of the product
price	decimal(10,2)	Not Null	Price of the product
stock_quantity	int	Not Null	Available stock quantity	0
category_id	int	Foreign Key (references categories.category_id)	Category to which the product belongs

Properties

• Primary Key: The product_id column is marked as the primary key.
• Foreign Key: The category_id column references the category_id in the categories table.
• Not Null: Ensures that product_name, price, and stock_quantity fields must have values.
• Default Value: The stock_quantity defaults to 0.

Example 4: Creating a Categories Table with Nested Indexes

Here’s an example of a table that stores product categories with index management:

• Table Name: categories
• Description: Stores category information for products.

Column Name	Data Type	Properties	Comments	Default Value
category_id	int	Primary Key, Auto Increment, Not Null	Unique identifier for category
category_name	varchar(100)	Not Null	Name of the category
parent_id	int	Foreign Key (self-reference), Null	References parent category ID

Properties

• Primary Key: The category_id column is marked as the primary key.
• Foreign Key: The parent_id column is a self-referencing foreign key, allowing a category to have a parent category.
• Not Null: The category_name field must have a value.

Adding Indexes

• Composite Index: You can add a composite index on category_id and parent_id to improve the performance of hierarchical category queries.

---

Summary

The table creation process in this UI allows users to define columns, data types, properties, and default values with ease. Indexes can be added to columns to improve query performance, and relationships can be defined between tables using foreign keys. The intuitive interface simplifies the creation and management of database tables.